/**
 * 该文件用来配置sql查询语句
 */

const sqlMap = {
  user: {
    find: `
      SELECT id,ccount,name,sex,add_time, 
      (SELECT Count(*) from users) as count 
      FROM users limit ? offset ?
    `,
    get_user: `SELECT id,ccount,name,sex FROM users WHERE id=?`,
    update: `UPDATE users SET name=?,pass=?,sex=? WHERE id=? and ccount=?`,
    update1: `UPDATE users SET name=?,sex=? WHERE id=? and ccount=?`,
    verify_name: `SELECT ccount FROM users WHERE ccount=?`,
    add: `INSERT INTO users (ccount,name,pass,sex,add_time,token) VALUES (?,?,?,?,?,?)`,
    remove: `
      DELETE users,message,comments,dot
      FROM users 
      LEFT JOIN message ON users.id=message.u_id
      LEFT JOIN comments ON message.id=comments.m_id
      LEFT JOIN dot ON message.id=dot.m_id
      WHERE users.id=?
    `,
    search: `SELECT id,ccount,name,sex,add_time FROM users WHERE ccount = ?`
  },
  admin: {
    login: `SELECT id,name,add_time,status,ip,is_open FROM admin WHERE pass=? and name=?`,
    update_ip: `UPDATE admin SET ip=? WHERE id =?`,
    list: `SELECT id,name,status,add_time,is_open FROM admin`,
    open_admin: `UPDATE admin SET is_open=? WHERE id=?`,
    remove: `DELETE FROM admin WHERE id=?`,
    add: `INSERT INTO admin (name,pass,is_open,status,add_time) VALUES (?,?,?,?,?)`,
    update: `UPDATE admin SET name=?,pass=?,is_open=? WHERE id=?`,
    update1: `UPDATE admin SET name=?,is_open=? WHERE id=?`,
    get_admin: `SELECT id,name,is_open FROM admin WHERE id=?`,
  },
  message: {
    find: `
      SELECT m.id,m.content,m.add_time,m.u_id as m_uid,u.ccount,
      (SELECT COUNT(*) FROM comments c WHERE c.m_id = m.id) as comments_num,
      (SELECT COUNT(*) FROM dot d WHERE d.m_id = m.id) as dot_num, 
      (SELECT COUNT(*) FROM message) as count
      FROM message m 
      LEFT JOIN users u ON m.u_id = u.id
      limit ? offset ?
    `,
    search: `
      SELECT m.id,m.content,m.add_time,m.u_id as m_uid,u.ccount,
      (SELECT COUNT(*) FROM comments c WHERE c.m_id = m.id) as comments_num,
      (SELECT COUNT(*) FROM dot d WHERE d.m_id = m.id) as dot_num 
      FROM message m 
      LEFT JOIN users u ON m.u_id = u.id
      WHERE m.u_id = ?
    `,
    update: ``,
    remove: `
      DELETE message,comments,dot
      FROM message 
      LEFT JOIN comments ON message.id=comments.m_id
      LEFT JOIN dot ON message.id=dot.m_id
      WHERE message.id=?
    `
  },
  comments: {
    find: `
      SELECT c.id, m.content as m_content, m.u_id as m_uid, c.u_id as c_uid, u.ccount as c_ccount, c.content as c_content, c.add_time, 
      (SELECT COUNT(*) FROM comments) as count 
      FROM comments c 
      LEFT JOIN message m ON c.m_id = m.id 
      LEFT JOIN users u ON c.u_id = u.id 
      limit ? offset ?
    `,
    remove: `DELETE FROM comments WHERE id=?`,
    search: `
      SELECT c.id,m.content as m_content,m.u_id as m_uid,c.u_id as c_uid,u.ccount as c_ccount,c.content as c_content,c.add_time
      FROM comments c 
      LEFT JOIN message m ON c.m_id = m.id 
      LEFT JOIN users u ON c.u_id = u.id
      WHERE c.u_id = ?
    `,
  },
  ccount: {
    sql: `
      SELECT COUNT(*) as num FROM users
      UNION All
      SELECT COUNT(*) FROM comments
      UNION ALL
      SELECT COUNT(*) FROM message
      UNION ALL
      SELECT COUNT(*) FROM dot
    `
  }
};

module.exports = sqlMap;